package cn.internship.modules.manage.dao;

import cn.internship.modules.manage.entity.vo.DeptTopList;
import cn.internship.modules.manage.entity.vo.IndustyTopList;
import cn.internship.modules.manage.entity.vo.MajorTopList;
import io.swagger.models.auth.In;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;


import java.util.List;

/**
 * @author zwr
 * @version 1.0
 * @description TODO
 * @date 2023/3/4 17:03
 */
@Mapper
public interface StatDao {

    @Select("select mi.*,r.sum from major_info mi\n" +
            "    left join(\n" +
            "    select ci.specialty_id,count(1) as sum from student_info si\n" +
            "     left join class_info ci on si.class_info_id = ci.id where si.deleted =0 group by ci.specialty_id\n" +
            "    ) r on mi.id = r.specialty_id order by r.sum desc limit 5")
    List<MajorTopList> queryMajorTop5();

    @Select("select di.*,r.sum from dept_info di\n" +
            "    left join(\n" +
            "    select mi.dept_id as dept_id,count(1) as sum from student_info si\n" +
            "     left join class_info ci on si.class_info_id = ci.id\n" +
            "     left join major_info mi on mi.id = ci.specialty_id\n" +
            "       where si.deleted =0 group by mi.dept_id\n" +
            "    ) r on di.id = r.dept_id order by r.sum desc limit 5")
    List<DeptTopList> queryDeptTop5();

    @Select("select industry_type,count(1) as num from stu_intern_company group by industry_type order by num desc limit 10")
    List<IndustyTopList> queryIndustyTop10();

    @Select("select count(1) from stu_intern_company")
    Integer countIndusty();

    @Select("select count(1) from stu_intern_company where type = 0")
    Integer countIndustyByCountry();

    @Select("select count(1) from stu_intern_company where type = 1")
    Integer countIndustyByPeople();

    @Select("select count(1) from internship_report")
    Integer reportSum();

    @Select("select count(1) from internship_report where score <60")
    Integer reportDifferSum();

    @Select("select count(1) from internship_report where score >= 60 and score<70")
    Integer reportPassSum();

    @Select("select count(1) from internship_report where score >= 70 and score<85")
    Integer reportGoodSum();

    @Select("select count(1) from internship_report where score >= 85 and score<100")
    Integer reportOutstandingSum();




}
